﻿Imports System.Data
Imports System.Data.Odbc
Imports System.Text
Imports System.Collections.Generic
Imports WSEntidades
Imports Utilerias
Imports WSFactory
Imports Npgsql
Imports MySql.Data.MySqlClient

Public Class UdeMDAL
    Private cadConex As String = ""

    Public Sub New(ByVal pCadConex As String)
        Me.cadConex = pCadConex
    End Sub

#Region "FuncionesUtilizables"

    Public Function existeUdeM(ByVal pDescrUdeM As String) As Boolean
        Dim numReg As Int16 = 0
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT COUNT(*) " & _
                                            "FROM udem " & _
                                            "WHERE DesUdeM = ?DesUdeM;"
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("?DesUdeM", pDescrUdeM)
                    myConn.Open()
                    numReg = Convert.ToInt16(myComm.ExecuteScalar())
                End Using
            End Using
            Return (numReg > 0)
        Catch ex As Exception
            Throw
        Finally
            numReg = Nothing
        End Try
    End Function

    Public Function registraUdeM(ByVal pUdeM As UdeMBE) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "INSERT INTO udem(DesUdeM, Status) " & _
                                            "VALUES (@DesUdeM,@Status);"
                    myComm.CommandType = System.Data.CommandType.Text
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@DesUdeM", pUdeM.DesUdeM)
                    Select Case pUdeM.Status
                        Case UdeMBE.EstatusUdeM.Activa
                            myComm.Parameters.AddWithValue("@Status", "A")
                        Case UdeMBE.EstatusUdeM.Inactiva
                            myComm.Parameters.AddWithValue("@Status", "I")
                        Case Else
                            myComm.Parameters.AddWithValue("@Status", "I")
                    End Select
                    myConn.Open()
                    If (myComm.ExecuteNonQuery() = 1) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function actualizaUdeM(ByVal pUdeM As UdeMBE, ByRef msg As String) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT count(*) " &
                                            "FROM udem " & _
                                            "WHERE IdUdeM <> @id " &
                                                "AND  DesUdeM = @descripcion;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@id", pUdeM.IdUdeM)
                    myComm.Parameters.AddWithValue("@descripcion", pUdeM.DesUdeM)
                    myConn.Open()
                    If (Convert.ToInt16(myComm.ExecuteScalar()) > 0) Then
                        msg &= "Existe otro registro con la Unidad de Medida especificada." & vbCrLf
                        Return False
                    End If

                    myComm.CommandText = "UPDATE udem " & _
                                            "SET DesUdeM = @DesUdeM, " & _
                                                "Status = @Status " & _
                                            "WHERE IdUdeM = @IdUdeM; "
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@DesUdeM", pUdeM.DesUdeM)
                    Select Case pUdeM.Status
                        Case UdeMBE.EstatusUdeM.Activa
                            myComm.Parameters.AddWithValue("@Status", "A")
                        Case UdeMBE.EstatusUdeM.Inactiva
                            myComm.Parameters.AddWithValue("@Status", "I")
                        Case Else
                            myComm.Parameters.AddWithValue("@Status", "I")
                    End Select
                    myComm.Parameters.AddWithValue("@IdUdeM", pUdeM.IdUdeM)
                    If (myComm.ExecuteNonQuery() = 1) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function cambiaEstatus(ByVal pIdUdeM As Integer, ByVal pSetActivo As Boolean) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "UPDATE udem " & _
                                            "SET Status = @Status " & _
                                            "WHERE IdUdeM = @IdUdeM; "
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Clear()
                    If (pSetActivo) Then
                        myComm.Parameters.AddWithValue("@Status", "A")
                    Else
                        myComm.Parameters.AddWithValue("@Status", "I")
                    End If
                    myComm.Parameters.AddWithValue("@IdUdeM", pIdUdeM)
                    myConn.Open()
                    If (myComm.ExecuteNonQuery() = 1) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function getUdeM(ByVal IdUdeM As Integer) As UdeMBE
        Dim tmpUdeM As UdeMBE
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT IdUdeM,  DesUdeM, Status " & _
                                            "FROM udem " & _
                                            "WHERE IdUdeM = @IdUdeM;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.AddWithValue("@IdUdeM", IdUdeM)
                    myConn.Open()
                    Using myDR As MySqlDataReader = myComm.ExecuteReader()
                        If (myDR.HasRows()) Then
                            myDR.Read()
                            tmpUdeM = UdeMFact.Build(myDR)
                        Else
                            tmpUdeM = Nothing
                        End If
                    End Using
                End Using
            End Using
            Return tmpUdeM

        Catch ex As Exception
            Throw
        Finally
            tmpUdeM = Nothing
        End Try
    End Function

    Public Function getListadoUdeM(ByVal pUdeM As String, ByVal estatus As Enumeraciones.estatusParaBusquedas,
                                   ByVal pRegistroInicial As Integer, ByVal pPageSize As Integer,
                                   ByVal pSortColumn As Enumeraciones.campoOrdenamiento, ByVal pSortDirection As Enumeraciones.tipoOrdenamiento,
                                   ByRef pTotalRegistros As Integer) As List(Of UdeMBE)
        Dim UdeMLs As List(Of UdeMBE) = New List(Of UdeMBE)
        Dim qryEstatus As String = ""
        Select Case estatus
            Case Enumeraciones.estatusParaBusquedas.Todos
                'No se pone filtro
            Case Enumeraciones.estatusParaBusquedas.Activos
                qryEstatus = " AND Status = 'A' "
            Case Enumeraciones.estatusParaBusquedas.Inactivos
                qryEstatus = " AND Status = 'I' "
        End Select

        pTotalRegistros = 0
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT SQL_CALC_FOUND_ROWS IdUdeM,  DesUdeM, Status  " & _
                                           "FROM udem " & _
                                           "WHERE DesUdeM LIKE @DesUdeM " &
                                                qryEstatus &
                                           Enumeraciones.getOrderByStatement(Enumeraciones.catalogo.UnidadMedida, pSortColumn, pSortDirection) &
                                           " LIMIT @regInic, @pageSize;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.AddWithValue("@DesUdeM", "%" & pUdeM & "%")
                    myComm.Parameters.AddWithValue("@regInic", pRegistroInicial)
                    myComm.Parameters.AddWithValue("@pageSize", pPageSize)
                    myConn.Open()
                    Using myDR As MySqlDataReader = myComm.ExecuteReader()
                        While myDR.Read()
                            Dim UdeM As UdeMBE = UdeMFact.Build(myDR)
                            If (Not UdeM Is Nothing) Then
                                UdeMLs.Add(UdeM)
                            End If
                            UdeM = Nothing
                        End While
                    End Using
                    Using myComm2 As MySqlCommand = myConn.CreateCommand()
                        myComm2.CommandType = CommandType.Text
                        myComm2.CommandText = "SELECT FOUND_ROWS();"
                        pTotalRegistros = Convert.ToInt32(myComm2.ExecuteScalar())
                    End Using
                End Using
            End Using
            Return UdeMLs
        Catch ex As Exception
            Throw
        Finally
            UdeMLs = Nothing
            qryEstatus = Nothing
        End Try
    End Function

#End Region

End Class
